﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Linq;

namespace QLSV_LINQ
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnThuchien_Click(object sender, EventArgs e)
        {
            QLSV_LINQ.Data.QLSinhvienDataContext db = new Data.QLSinhvienDataContext();
            Table<QLSV_LINQ.Data.SINH_VIEN> Sinhviens = db.GetTable<QLSV_LINQ.Data.SINH_VIEN>();
            Table<QLSV_LINQ.Data.KHOA> Khoas=db.GetTable<QLSV_LINQ.Data.KHOA>();
            var query = Sinhviens.Where(sv => sv.Ten_sinh_vien.StartsWith("T")).OrderBy(sv => sv.Ten_sinh_vien)
                .Select(S => new { S.Ma_khoa, S.Ma_sinh_vien, S.Ho_sinh_vien, S.Ten_sinh_vien });

            //var query = from s in Sinhviens select s;

            //var query = from s in Sinhviens
            //            where s.Ten_sinh_vien.StartsWith("H")
            //            orderby s.Ten_sinh_vien
            //            select new { s.Ma_khoa, s.Ma_sinh_vien, s.Ho_sinh_vien, s.Ten_sinh_vien };

            //var query = Sinhviens.Join(Khoas, s => s.Ma_khoa, k => k.Ma_khoa, (s, k) => new { k.Ma_khoa, k.Ten_khoa, s.Ho_sinh_vien, s.Ten_sinh_vien, s.Gioi_tinh }).OrderBy(k => k.Ma_khoa);

            //var query = from s in Sinhviens
            //            join tt in Khoas
            //            on s.Ma_khoa equals tt.Ma_khoa
            //            select new { s.Ma_khoa, tt.Ten_khoa, s.Ma_sinh_vien, s.Ten_sinh_vien };

            //var query = Sinhviens.GroupBy(s => s.Ma_khoa).Select(s => new { s.Key, So_sinh_vien = s.Count() });

            //var query = from k in Khoas
            //            where !(from sv in Sinhviens select sv.Ma_khoa).Contains(k.Ma_khoa)
            //            select k;


            //var query = (Sinhviens.Join(Khoas, sv => sv.Ma_khoa, k => k.Ma_khoa, (sv, k) => new { sv.Hoc_bong, k.Ma_khoa, k.Ten_khoa })
            //    .GroupBy(k => new { k.Ma_khoa, k.Ten_khoa })
            //    .Select(k => new { k.Key.Ma_khoa, k.Key.Ten_khoa, Sosinhvien = k.Count() })).Skip(4).Take(3);

            //var query = db.sp_DemsoSinhvientheoKhoa("1");
            dtgSV.DataSource = query;
        }
    }
}
